const express = require('express');
const router = express.Router();
const { pool } = require('../config/db');
const auth = require('../middleware/auth');

// 获取教师统计数据
router.get('/stats', auth, async (req, res) => {
  try {
    // 验证用户权限
    if (req.user.role !== 'teacher') {
      return res.status(403).json({ message: '权限不足，只有教师可以访问此接口' });
    }

    const teacherId = req.user.id;
    console.log('获取教师统计数据，教师ID:', teacherId);

    // 获取教师课程数量
    const [courseResult] = await pool.query(
      `SELECT COUNT(*) as courseCount 
       FROM courses 
       WHERE teacherId = ?`,
      [teacherId]
    );
    const courseCount = courseResult[0]?.courseCount || 0;
    
    // 创建一个包含所有统计数据的对象
    const statsData = {
      courseCount,
      attendanceRate: 0,
      pendingLeaves: 0,
      unreadMessages: 0,
      gradeProgress: 0,
      todoCount: 0
    };

    // 获取考勤率 - 使用attendance表计算
    try {
      const [attendanceResult] = await pool.query(`
        SELECT 
          ROUND(
            (COUNT(CASE WHEN a.status = 'present' OR a.status = 'late' THEN 1 ELSE NULL END) * 100.0) / 
            NULLIF(COUNT(*), 0)
          ) as attendanceRate
        FROM attendance a
        JOIN courses c ON a.courseId = c.id
        WHERE c.teacherId = ?
      `, [teacherId]);
      
      statsData.attendanceRate = attendanceResult[0]?.attendanceRate || 0;
    } catch (err) {
      console.log('获取考勤率失败:', err.message);
      // 保持默认值
    }

    // 获取待处理请假数量 - 创建leaves表
    try {
      // 检查leaves表是否存在
      const [tablesResult] = await pool.query(`
        SELECT COUNT(*) as tableExists 
        FROM information_schema.tables 
        WHERE table_schema = DATABASE() 
        AND table_name = 'leaves'
      `);
      
      if (tablesResult[0].tableExists > 0) {
        // 修改查询，通过课程关联到教师
        const [leaveResult] = await pool.query(`
          SELECT COUNT(*) as pendingLeaves
          FROM leaves l
          JOIN courses c ON l.courseId = c.id
          WHERE c.teacherId = ? AND l.status = 'pending'
        `, [teacherId]);
        
        statsData.pendingLeaves = leaveResult[0]?.pendingLeaves || 0;
      } else {
        // 如果表不存在，创建表
        await pool.query(`
          CREATE TABLE IF NOT EXISTS leaves (
            id INT AUTO_INCREMENT PRIMARY KEY,
            studentId INT NOT NULL,
            courseId INT NOT NULL,
            startDate DATE NOT NULL,
            endDate DATE NOT NULL,
            reason TEXT,
            status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
            createTime DATETIME DEFAULT CURRENT_TIMESTAMP,
            updateTime DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (studentId) REFERENCES users(id),
            FOREIGN KEY (courseId) REFERENCES courses(id)
          )
        `);
        console.log('创建leaves表成功');
      }
    } catch (err) {
      console.log('处理请假表失败:', err.message);
    }

    // 获取未读消息数量 - 创建messages表
    try {
      // 检查messages表是否存在
      const [tablesResult] = await pool.query(`
        SELECT COUNT(*) as tableExists 
        FROM information_schema.tables 
        WHERE table_schema = DATABASE() 
        AND table_name = 'messages'
      `);
      
      if (tablesResult[0].tableExists > 0) {
        const [messageResult] = await pool.query(`
          SELECT COUNT(*) as unreadMessages
          FROM messages
          WHERE receiverId = ? AND isRead = 0
        `, [teacherId]);
        
        statsData.unreadMessages = messageResult[0]?.unreadMessages || 0;
      } else {
        // 如果表不存在，创建表
        await pool.query(`
          CREATE TABLE IF NOT EXISTS messages (
            id INT AUTO_INCREMENT PRIMARY KEY,
            senderId INT NOT NULL,
            receiverId INT NOT NULL,
            title VARCHAR(255),
            content TEXT,
            isRead TINYINT(1) DEFAULT 0,
            createTime DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (senderId) REFERENCES users(id),
            FOREIGN KEY (receiverId) REFERENCES users(id)
          )
        `);
        console.log('创建messages表成功');
      }
    } catch (err) {
      console.log('处理消息表失败:', err.message);
    }

    // 获取成绩录入进度 - 创建grades表
    try {
      // 检查grades表是否存在
      const [tablesResult] = await pool.query(`
        SELECT COUNT(*) as tableExists 
        FROM information_schema.tables 
        WHERE table_schema = DATABASE() 
        AND table_name = 'grades'
      `);
      
      if (tablesResult[0].tableExists > 0) {
        const [gradeResult] = await pool.query(`
          SELECT 
            ROUND(
              (SUM(CASE WHEN g.score IS NOT NULL THEN 1 ELSE 0 END) * 100.0) / 
              NULLIF(COUNT(*), 0)
            ) as gradeProgress
          FROM grades g
          JOIN courses c ON g.courseId = c.id
          WHERE c.teacherId = ?
        `, [teacherId]);
        
        statsData.gradeProgress = gradeResult[0]?.gradeProgress || 0;
      } else {
        // 如果表不存在，创建表
        await pool.query(`
          CREATE TABLE IF NOT EXISTS grades (
            id INT AUTO_INCREMENT PRIMARY KEY,
            studentId INT NOT NULL,
            courseId INT NOT NULL,
            score DECIMAL(5,2),
            term VARCHAR(20),
            createTime DATETIME DEFAULT CURRENT_TIMESTAMP,
            updateTime DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (studentId) REFERENCES users(id),
            FOREIGN KEY (courseId) REFERENCES courses(id)
          )
        `);
        console.log('创建grades表成功');
      }
    } catch (err) {
      console.log('处理成绩表失败:', err.message);
    }

    // 获取待办事项数量
    try {
      let todoCount = 0;
      
      // 待处理的请假申请
      if (await tableExists('leaves')) {
        // 修改查询，通过课程关联到教师
        const [leaveResult] = await pool.query(`
          SELECT COUNT(*) as leaveCount
          FROM leaves l
          JOIN courses c ON l.courseId = c.id
          WHERE c.teacherId = ? AND l.status = 'pending'
        `, [teacherId]);
        
        todoCount += leaveResult[0]?.leaveCount || 0;
      }
      
      // 进行中的考勤会话
      if (await tableExists('teacher_signin_sessions')) {
        const [sessionResult] = await pool.query(`
          SELECT COUNT(*) as sessionCount
          FROM teacher_signin_sessions
          WHERE teacherId = ? AND status = 'active'
        `, [teacherId]);
        
        todoCount += sessionResult[0]?.sessionCount || 0;
      }
      
      // 添加需要录入成绩的课程数量
      if (await tableExists('grades') && await tableExists('courses') && await tableExists('users')) {
        const [gradeResult] = await pool.query(`
          SELECT COUNT(DISTINCT c.id) as gradeCount
          FROM courses c
          JOIN course_class_relation ccr ON c.id = ccr.courseId
          JOIN users u ON u.classId = ccr.classId AND u.role = 'student'
          LEFT JOIN grades g ON g.studentId = u.id AND g.courseId = c.id
          WHERE c.teacherId = ?
          GROUP BY c.id
          HAVING COUNT(DISTINCT u.id) > COUNT(DISTINCT g.id)
        `, [teacherId]);
        
        // 修改这里，直接计算返回的行数
        todoCount += gradeResult.length || 0;
      }
      
      statsData.todoCount = todoCount;
    } catch (err) {
      console.log('获取待办事项数据失败:', err.message);
    }

    // 返回统计数据
    res.json({
      success: true,
      data: statsData
    });
  } catch (error) {
    console.error('获取教师统计数据失败:', error);
    res.status(500).json({ 
      success: false,
      message: '服务器错误',
      error: error.message
    });
  }
});

// 辅助函数：检查表是否存在
async function tableExists(tableName) {
  const [result] = await pool.query(`
    SELECT COUNT(*) as count 
    FROM information_schema.tables 
    WHERE table_schema = DATABASE() 
    AND table_name = ?
  `, [tableName]);
  
  return result[0].count > 0;
}

// 获取教师待办事项
router.get('/todos', auth, async (req, res) => {
  try {
    // 验证用户权限
    if (req.user.role !== 'teacher') {
      return res.status(403).json({ message: '权限不足，只有教师可以访问此接口' });
    }

    const teacherId = req.user.id;
    console.log('获取教师待办事项，教师ID:', teacherId);

    const todos = [];

    // 获取待处理的请假申请
    try {
      const [leaveRows] = await pool.query(`
        SELECT 
          l.id,
          u.name as studentName,
          c.name as courseName,
          l.startDate,
          l.endDate,
          l.reason,
          l.status,
          l.createTime
        FROM 
          leaves l
        JOIN 
          users u ON l.studentId = u.id
        JOIN 
          courses c ON l.courseId = c.id
        WHERE 
          c.teacherId = ? AND l.status = 'pending'
        ORDER BY 
          l.createTime DESC
      `, [teacherId]);

      // 将请假申请添加到待办事项
      leaveRows.forEach(leave => {
        todos.push({
          id: `leave_${leave.id}`,
          type: 'leave',
          content: `${leave.studentName}申请请假《${leave.courseName}》`,
          createTime: new Date(leave.createTime).toISOString().split('T')[0],
          details: {
            studentName: leave.studentName,
            courseName: leave.courseName,
            startDate: leave.startDate,
            endDate: leave.endDate,
            reason: leave.reason
          }
        });
      });
    } catch (err) {
      console.log('获取请假申请失败:', err.message);
    }

    // 获取进行中的考勤会话
    try {
      const [sessionRows] = await pool.query(`
        SELECT 
          s.id,
          c.name as courseName,
          cl.className,
          s.date,
          DATE_FORMAT(s.startTime, '%H:%i') as startTime,
          s.status
        FROM 
          teacher_signin_sessions s
        JOIN 
          courses c ON s.courseId = c.id
        JOIN 
          classes cl ON s.classId = cl.id
        WHERE 
          s.teacherId = ? AND s.status = 'active'
        ORDER BY 
          s.startTime DESC
      `, [teacherId]);

      // 将进行中的考勤会话添加到待办事项
      sessionRows.forEach(session => {
        todos.push({
          id: `attendance_${session.id}`,
          type: 'attendance',
          content: `${session.className}《${session.courseName}》考勤进行中`,
          createTime: session.date,
          details: {
            className: session.className,
            courseName: session.courseName,
            startTime: session.startTime,
            date: session.date
          }
        });
      });
    } catch (err) {
      console.log('获取考勤会话失败:', err.message);
    }

    // 获取需要录入成绩的课程
    try {
      const [gradeRows] = await pool.query(`
        SELECT 
          c.id,
          c.name as courseName,
          COUNT(DISTINCT u.id) as totalStudents,
          COUNT(DISTINCT g.id) as gradedStudents
        FROM 
          courses c
        JOIN 
          course_class_relation ccr ON c.id = ccr.courseId
        JOIN 
          users u ON u.classId = ccr.classId AND u.role = 'student'
        LEFT JOIN 
          grades g ON g.studentId = u.id AND g.courseId = c.id
        WHERE 
          c.teacherId = ?
        GROUP BY 
          c.id
        HAVING 
          COUNT(DISTINCT u.id) > COUNT(DISTINCT g.id)
        ORDER BY 
          (COUNT(DISTINCT g.id) / COUNT(DISTINCT u.id)) ASC
      `, [teacherId]);

      // 将需要录入成绩的课程添加到待办事项
      gradeRows.forEach(grade => {
        const progress = Math.round((grade.gradedStudents / grade.totalStudents) * 100);
        todos.push({
          id: `grade_${grade.id}`,
          type: 'grade',
          content: `《${grade.courseName}》成绩待录入 (${progress}%)`,
          createTime: new Date().toISOString().split('T')[0], // 使用当前日期
          details: {
            courseName: grade.courseName,
            totalStudents: grade.totalStudents,
            gradedStudents: grade.gradedStudents,
            progress: progress
          }
        });
      });
    } catch (err) {
      console.log('获取成绩录入情况失败:', err.message);
    }

    res.json({
      success: true,
      data: todos
    });
  } catch (error) {
    console.error('获取教师待办事项失败:', error);
    res.status(500).json({ 
      success: false,
      message: '服务器错误',
      error: error.message
    });
  }
});

// 获取教师通知
router.get('/notices', auth, async (req, res) => {
  try {
    // 验证用户权限
    if (req.user.role !== 'teacher') {
      return res.status(403).json({ message: '权限不足，只有教师可以访问此接口' });
    }

    const teacherId = req.user.id;
    console.log('获取教师通知，教师ID:', teacherId);

    // 检查notices表是否存在
    const noticesExists = await tableExists('notices');
    
    if (noticesExists) {
      // 获取发送给所有人或特定教师的通知
      const [noticeRows] = await pool.query(`
        SELECT 
          id,
          title,
          content,
          DATE_FORMAT(createTime, '%Y-%m-%d') as createTime,
          publisherId,
          targetRole,
          targetId
        FROM 
          notices
        WHERE 
          (targetRole = 'all' OR targetRole = 'teacher') 
          AND (targetId IS NULL OR targetId = ? OR targetId = 0)
        ORDER BY 
          createTime DESC
        LIMIT 10
      `, [teacherId]);
      
      res.json({
        success: true,
        data: noticeRows
      });
    } else {
      // 如果表不存在，创建表
      await pool.query(`
        CREATE TABLE IF NOT EXISTS notices (
          id INT AUTO_INCREMENT PRIMARY KEY,
          title VARCHAR(255) NOT NULL,
          content TEXT,
          createTime DATETIME DEFAULT CURRENT_TIMESTAMP,
          publisherId INT,
          targetRole ENUM('all', 'student', 'teacher', 'counselor', 'admin') DEFAULT 'all',
          targetId INT,
          isRead TINYINT(1) DEFAULT 0,
          FOREIGN KEY (publisherId) REFERENCES users(id)
        )
      `);
      console.log('创建notices表成功');
      
      res.json({
        success: true,
        data: []
      });
    }
  } catch (error) {
    console.error('获取教师通知失败:', error);
    res.status(500).json({ 
      success: false,
      message: '服务器错误',
      error: error.message
    });
  }
});

module.exports = router;